IF OBJECT_ID('dbo.fnGetPeriodBudget') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetPeriodBudget
    IF OBJECT_ID('dbo.fnGetPeriodBudget') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetPeriodBudget >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetPeriodBudget >>>'
END
go

-- =======================================================================
-- Function Name: dbo.fnGetPeriodBudget()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/20/2007
--
-- Description: Returns Table of BudgetPeriods and Amounts
--             for a given Search Date range for Reports.
--
-- Parameters:  @StartDate, @EndDate 
--              Search Params for Reports
--              
-- Returns:     TABLE (see defn below) 
--
-- SampleCall:
-- SELECT * FROM dbo.fnGetPeriodBudget ( @StartDate, @EndDate )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/20/07     KC        Created
-- 9/7/07      KC        Changed for new version of AgrAgreement_Budget
-- 9/10/07     KC        Added SequenceYear
--
-- =======================================================================
CREATE FUNCTION dbo.fnGetPeriodBudget
(
     @StartDate     DATETIME, 
     @EndDate       DATETIME
)
RETURNS @PeriodBudget TABLE
(
     AgreementId         INTEGER,
     BudgetYear          INTEGER,
     SequenceYear        INTEGER,
     FundNumber          VARCHAR(20),
     BudgetStartDate     DATETIME,
     BudgetEndDate       DATETIME,
     BudgetDirectCost    MONEY,
     BudgetIndirectCost  MONEY,
     IndirectCostRate    FLOAT,
     BudgetViewType      VARCHAR(10)
)
AS
BEGIN
     INSERT    @PeriodBudget
     SELECT    SRCHBGT.AgreementId,
               SRCHBGT.BudgetYear,
               VBGT.SequenceYear,
               VBGT.FundNumber,
               VBGT.BudgetStartDate,
               VBGT.BudgetEndDate,
               VBGT.BudgetDirectCost,
               VBGT.BudgetIndirectCost,
               VBGT.IndirectCostRate,
               VBGT.BudgetViewType
     FROM               
     (SELECT   VWB.AgreementId,
               MAX(VWB.MAXYear) AS BudgetYear
     FROM          
     (SELECT    AgreementId,
               MAX(BudgetYear) AS MAXYear
     FROM      dbo.AgrAgreement_Budget
     WHERE     (@StartDate BETWEEN BudgetStartDate AND BudgetEndDate OR
               @EndDate BETWEEN BudgetStartDate AND BudgetEndDate )
     GROUP BY AgreementId          
     UNION
     SELECT    AgreementId,
               MAX(BudgetYear) AS MAXYear
     FROM      dbo.AgrAgreement_Budget
     WHERE     BudgetEndDate < @EndDate
     GROUP BY AgreementId 
     UNION
     SELECT    AgreementId,
               MIN(BudgetYear) AS MAXYear
     FROM      dbo.AgrAgreement_Budget
     WHERE     BudgetEndDate < @EndDate
     GROUP BY AgreementId ) VWB
     GROUP BY VWB.AgreementId ) SRCHBGT INNER JOIN dbo.AgrAgreement_Budget VBGT ON
                    SRCHBGT.AgreementId = VBGT.AgreementId AND SRCHBGT.BudgetYear = VBGT.BudgetYear 
     
     RETURN
END
go
IF OBJECT_ID('dbo.fnGetPeriodBudget') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetPeriodBudget >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetPeriodBudget >>>'
go